﻿/*
Autor...: Ivan M. Kruger
Empresa.: Home Kruger
Data....: 03/06/2011
*/

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using System.Data.OleDb;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data.OracleClient;
using FirebirdSql.Data.FirebirdClient;

namespace ik.DB
{
    #region "enum"
    /// <summary>
    /// tipo do SGBD
    /// </summary>
    public enum enSGBD
    {
        SQLServer = 0,
        Firebird = 1,
        OleDb = 2,
        MySql = 3,
        Oracle = 4
    }

    public enum enSchema
    {
        MetaDataCollections,
        DataSourceInformation,
        DataTypes,
        Restrictions,
        ReservedWords,
        Users,
        Databases,
        Tables,
        Columns,
        AllColumns,
        ColumnSetColumns,
        StructuredTypeMembers,
        Views,
        ViewColumns,
        ProcedureParameters,
        Procedures,
        ForeignKeys,
        IndexColumns,
        Indexes,
        UserDefinedTypes
    }
    #endregion

    /// <summary>
    /// classe de tratamento de dados Firebird, SQLServer e OleDb
    /// </summary>
    /// abstract -> não permite instanciar a classse
    public abstract class clDB : ik.FunctionsDB.clFunctionsDB
    {
        #region "Propriedades, parametros e classe de funções para o banco"
        //public delegate void ProgreeDelegate();
        //public event ProgreeDelegate OnProgress;

        /// <summary>
        /// tipo do SGBD
        /// </summary>
        public enSGBD SGBD { get; protected set; }
        /// <summary>
        /// string de conexão
        /// </summary>
        /// protected -> a classe filha pode manipular o valor do pai;
        protected String cnnString { get; set; }
        /// <summary>
        /// conexão principal com o banco
        /// </summary>
        protected IDbConnection cnnDB = null;
        /// <summary>
        /// lista de parametros para procedures - SQLServer
        /// </summary>
        private List<SqlParameter> _SqlParms = new List<SqlParameter>();
        /// <summary>
        /// lista de parametros para procedures - Firebird
        /// </summary>
        private List<FbParameter> _FbParms = new List<FbParameter>();
        /// <summary>
        /// lista de parametros para procedures - OleDb
        /// </summary>
        private List<OleDbParameter> _OleParms = new List<OleDbParameter>();
        /// <summary>
        /// lista de parametros para procedures - MySql
        /// </summary>
        private List<MySqlParameter> _MySqlParms = new List<MySqlParameter>();
        /// <summary>
        /// lista de parametros para procedures - Oracle
        /// </summary>
        private List<OracleParameter> _OraParms = new List<OracleParameter>();

        //private IDbDataParameter[] _ParmsGeneric = null;

        #endregion

        #region "construtor da classe"
        /// <summary>
        /// construtor da classe
        /// </summary>
        /// <param name="side">tipo do sgbd</param>
        public clDB(enSGBD sgbd)
        {
            SGBD = sgbd;
        }


        /// <summary>
        /// destrutor da classe
        /// </summary>
        ~clDB()
        {
            //try
            //{
            //    CloseConnection();
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
        }
        #endregion

        #region "ToString"
        /// <summary>
        /// class information
        /// </summary>
        /// <returns>String</returns>
        public override String ToString()
        {
            return "Class name: ik.DB.clDB \n\n" +
                   "Class version: " + Environment.Version.ToString() + "\n" +
                   "OS version: " + Environment.OSVersion.ToString() + "\n" +
                   "Computer name: " + Environment.MachineName + "\n" +
                   "Rights: (c) 2011 - by Ivan Kruger";
        }
        #endregion

        #region "CloseConnection"
        /// <summary>
        /// fecha a conexão com banco de dados Firebird
        /// </summary>
        public void CloseConnection()
        {
            try
            {
                if (cnnDB.State == ConnectionState.Open)
                {
                    cnnDB.Close();
                    if (SGBD == enSGBD.Firebird)
                        FbConnection.ClearPool((FbConnection)cnnDB);
                    else if (SGBD == enSGBD.SQLServer)
                        SqlConnection.ClearPool((SqlConnection)cnnDB);
                    else if (SGBD == enSGBD.Oracle)
                        OracleConnection.ClearPool((OracleConnection)cnnDB);

                    cnnDB.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetStateConnection -> retorna o status da conexão"
        /// <summary>
        /// retorna o status da conexão
        /// </summary>
        /// <returns>ConnectionState</returns>
        public ConnectionState GetStateConnection()
        {
            try
            {
                if (cnnDB == null)
                    return ConnectionState.Broken;
                return cnnDB.State;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetDataSet -> DataSet"
        /// <summary>
        /// Retorna um DataSet
        /// </summary>
        /// <param name="sql">instrução sql a executar</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(String sql)
        {
            return GetDataSet(sql, 0);
        }
        /// <summary>
        /// Retorna um DataSet
        /// </summary>
        /// <param name="sql">instrução sql a executar</param>
        /// <param name="TimeOut">Tempo máximo para execução [0-infinito]</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(String sql, Int32 TimeOut)
        {
            for (int i = 0; i < 2; i++)
            {
                try
                {
                    IDataAdapter _da = null;
                    DataSet _ds = new DataSet();

                    switch (SGBD)
                    {
                        case enSGBD.Firebird:
                            _da = new FbDataAdapter(sql, (FbConnection)cnnDB);
                            ((FbDataAdapter)_da).SelectCommand.CommandTimeout = TimeOut;
                            break;
                        case enSGBD.SQLServer:
                            _da = new SqlDataAdapter(sql, (SqlConnection)cnnDB);
                            ((SqlDataAdapter)_da).SelectCommand.CommandTimeout = TimeOut;
                            break;
                        case enSGBD.OleDb:
                            _da = new OleDbDataAdapter(sql, (OleDbConnection)cnnDB);
                            ((OleDbDataAdapter)_da).SelectCommand.CommandTimeout = TimeOut;
                            break;
                        case enSGBD.MySql:
                            _da = new MySqlDataAdapter(sql, (MySqlConnection)cnnDB);
                            ((MySqlDataAdapter)_da).SelectCommand.CommandTimeout = TimeOut;
                            break;
                        case enSGBD.Oracle:
                            _da = new OracleDataAdapter(sql, (OracleConnection)cnnDB);
                            ((OracleDataAdapter)_da).SelectCommand.CommandTimeout = TimeOut;
                            break;
                        default:
                            throw new Exception("SGBD não identificado..");
                    }

                    _da.FillSchema(_ds, SchemaType.Mapped);
                    _da.Fill(_ds);
                    _da = null;

                    return _ds;
                }
                catch (Exception ex)
                {
                    if (i == 0)
                        continue;
                    throw ex;
                }
            }
            return null;
        }
        #endregion

        #region "GetDataReader -> retorna DataReader"
        /// <summary>
        /// executa instrução sql com retorno de um datareader
        /// </summary>
        /// <param name="sql">instrução sql</param>
        /// <param name="nrCnn">número da conexão {0 - 1}</param>
        /// <returns>FbDataReader</returns>
        public IDataReader GetDataReader(String sql)
        {
            try
            {
                IDbCommand _cmd = null;
                if (SGBD == enSGBD.Firebird)
                {
                    _cmd = new FbCommand(sql, (FbConnection)cnnDB);
                    ((FbCommand)_cmd).CommandTimeout = 0;
                }
                else if (SGBD == enSGBD.SQLServer)
                {
                    _cmd = new SqlCommand(sql, (SqlConnection)cnnDB);
                    ((SqlCommand)_cmd).CommandTimeout = 0;
                }
                else if (SGBD == enSGBD.OleDb)
                {
                    _cmd = new OleDbCommand(sql, (OleDbConnection)cnnDB);
                    ((OleDbCommand)_cmd).CommandTimeout = 0;
                }
                else if (SGBD == enSGBD.MySql)
                {
                    _cmd = new MySqlCommand(sql, (MySqlConnection)cnnDB);
                    ((MySqlCommand)_cmd).CommandTimeout = 0;
                }
                else if (SGBD == enSGBD.Oracle)
                {
                    _cmd = new OracleCommand(sql, (OracleConnection)cnnDB);
                    ((OracleCommand)_cmd).CommandTimeout = 0;
                }
                return _cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetField -> object"
        /// <summary>
        /// Retorna dados do banco de dados.
        /// </summary>
        /// <param name="tab">Tabela</param>
        /// <param name="whr">Clausula where (variável/campo)</param>
        /// <returns>Boleano</returns>
        public Boolean GetField(String tab, String whr)
        {
            try
            {
                return GetField(tab, whr, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// retorna dados do banco de dados.
        /// </summary>
        /// <param name="cnnSql">Conexão do sql-server</param>
        /// <param name="tab">tabela do banco</param>
        /// <param name="whr">clausula where</param>
        /// <param name="vet">textbox/label, campo</param>
        /// <returns></returns>
        public Boolean GetField(String tab, String whr, params object[] vet)
        {
            String cmp = "";
            try
            {
                if (vet != null)
                {
                    for (int i = 0; i < vet.Length; i++)
                    {
                        if (i % 2 != 0)
                            cmp = cmp + vet[i].ToString() + ", ";
                    }
                }
                if (cmp != "")
                    cmp = cmp.Substring(0, cmp.Length - 2);
                else
                    cmp = "TOP 1 1";

                DataSet _oDS = GetDataSet(String.Format("SELECT {0} FROM {1} WHERE {2}", cmp, tab, whr), 0);

                if (cmp == "TOP 1 1")
                    if (_oDS.Tables[0].Rows.Count == 0)
                        return false;
                    else
                        return true;

                if (vet.Length > 0)
                    if (_oDS.Tables[0].Rows.Count != 0)
                    {
                        for (int i = 0; i < vet.Length; i++)
                        {
                            if (i % 2 == 0)
                            {
                                if (vet[i].GetType() == typeof(System.Windows.Forms.TextBox))
                                    ((System.Windows.Forms.TextBox)vet[i]).Text = _oDS.Tables[0].Rows[0][i / 2].ToString();

                                if (vet[i].GetType() == typeof(System.Windows.Forms.Label))
                                    ((System.Windows.Forms.Label)vet[i]).Text = _oDS.Tables[0].Rows[0][i / 2].ToString();

                                if (vet[i].GetType() == typeof(System.Windows.Forms.CheckBox))
                                    ((System.Windows.Forms.CheckBox)vet[i]).Checked = (bool)_oDS.Tables[0].Rows[0][i / 2];
                            }
                        }
                    }
                    else
                    {
                        for (int i = 0; i < vet.Length; i++)
                        {
                            if (i % 2 == 0)
                            {
                                if (vet[i].GetType() == typeof(System.Windows.Forms.TextBox))
                                    ((System.Windows.Forms.TextBox)vet[i]).Text = "";

                                if (vet[i].GetType() == typeof(System.Windows.Forms.Label))
                                    ((System.Windows.Forms.Label)vet[i]).Text = "";

                                if (vet[i].GetType() == typeof(System.Windows.Forms.CheckBox))
                                    ((System.Windows.Forms.CheckBox)vet[i]).Checked = false;
                            }
                        }
                    }

                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetDataView -> retorna data view de uma tabela"
        /// <summary>
        /// retorna uma dataView de uma tabela
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="filter">filtro</param>
        /// <returns>DataView</returns>
        public DataView GetDataView(DataTable dt, String filter)
        {
            try
            {
                return GetDataView(dt, filter, "", DataViewRowState.CurrentRows);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// retorna uma dataView de uma tabela
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="filter">filtro</param>
        /// <param name="sort">ordem</param>
        /// <param name="dvrs">tipo do state</param>
        /// <returns>DataView</returns>
        public DataView GetDataView(DataTable dt, String filter, String sort, DataViewRowState dvrs)
        {
            try
            {
                return new DataView(dt, filter, sort, dvrs);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetDataAdapter -> Retorna por ref. DataSet e DataAdapter"
        /// <summary>
        /// retorna por ref DataSet e DataAdater; opcional com insert de campo identity
        /// </summary>
        /// <param name="sql">instrução t-sql</param>
        /// <param name="ds">ref para DataSet</param>
        /// <param name="da">ref oara IdbDataAdapter</param>
        public void GetDataAdapter(String sql, ref DataSet ds, ref IDbDataAdapter da)
        {
            try
            {
                GetDataAdapter(sql, ref ds, ref da, null, null, SqlDbType.Bit, 0);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// retorna por ref DataSet e DataAdater; opcional com insert de campo identity
        /// </summary>
        /// <param name="sql">instrução t-sql</param>
        /// <param name="ds">ref para DataSet</param>
        /// <param name="da">ref oara IdbDataAdapter</param>
        /// <param name="tab">tabela para insert identity</param>
        /// <param name="columnName">coluna para o insert identity</param>
        /// <param name="columnType">tipo do data da coluna (ex: SqlDbType.Int)</param>
        /// <param name="columnSize">tamanho da coluna coluna</param>
        public void GetDataAdapter(String sql, ref DataSet ds, ref IDbDataAdapter da, String tab, String columnName, Object columnType, Int32 columnSize)
        {
            try
            {
                DbCommandBuilder _cbld = null;
                DbCommand _cmdIns = null;
                DbCommand _cmdUpd = null;
                DbCommand _cmdDel = null;

                if (SGBD == enSGBD.Firebird)
                {
                    da = new FbDataAdapter(sql, (FbConnection)cnnDB);
                    _cbld = new FbCommandBuilder((FbDataAdapter)da);
                }
                else if (SGBD == enSGBD.SQLServer)
                {
                    da = new SqlDataAdapter(sql, (SqlConnection)cnnDB);
                    _cbld = new SqlCommandBuilder((SqlDataAdapter)da);
                }
                else if (SGBD == enSGBD.OleDb)
                {
                    da = new OleDbDataAdapter(sql, (OleDbConnection)cnnDB);
                    _cbld = new OleDbCommandBuilder((OleDbDataAdapter)da);
                }
                else if (SGBD == enSGBD.MySql)
                {
                    da = new MySqlDataAdapter(sql, (MySqlConnection)cnnDB);
                    _cbld = new MySqlCommandBuilder((MySqlDataAdapter)da);
                }
                else if (SGBD == enSGBD.Oracle)
                {
                    da = new OracleDataAdapter(sql, (OracleConnection)cnnDB);
                    _cbld = new OracleCommandBuilder((OracleDataAdapter)da);
                }

                _cbld.ConflictOption = ConflictOption.CompareRowVersion;
                da.FillSchema(ds, SchemaType.Mapped);
                da.Fill(ds);

                _cmdIns = _cbld.GetInsertCommand();
                _cmdUpd = _cbld.GetUpdateCommand();
                _cmdDel = _cbld.GetDeleteCommand();

                _cbld.DataAdapter = null;
                _cbld = null;

                da.InsertCommand = _cmdIns;
                da.UpdateCommand = _cmdUpd;
                da.DeleteCommand = _cmdDel;

                #region "identity insert"
                if (columnName != null && tab != null)
                {
                    String _sqlCmdIns = "";
                    _sqlCmdIns = _cmdIns.CommandText.Substring(0, _cmdIns.CommandText.LastIndexOf(')')) + ", @p_Identity)";
                    _sqlCmdIns = _sqlCmdIns.Substring(0, _sqlCmdIns.IndexOf(") VALUES (")) + ", " + columnName + ") VALUES (" + _sqlCmdIns.Substring(_sqlCmdIns.IndexOf(") VALUES (") + 10);

                    _cmdIns.CommandText = "SET IDENTITY_INSERT " + tab + " ON; " + _sqlCmdIns;
                    if (SGBD == enSGBD.Firebird)
                        _cmdIns.Parameters.Add(new FbParameter("@p_Identity", (FbDbType)columnType, 0, columnName));
                    else if (SGBD == enSGBD.SQLServer)
                        _cmdIns.Parameters.Add(new SqlParameter("@p_Identity", (SqlDbType)columnType, 0, columnName));
                    else if (SGBD == enSGBD.OleDb)
                        _cmdIns.Parameters.Add(new OleDbParameter("@p_Identity", (OleDbType)columnType, 0, columnName));

                    ds.EnforceConstraints = false;
                    //col.ReadOnly = false;

                    da.InsertCommand = _cmdIns;
                }
                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetObject -> retorna {DataSet | DataTable | DataReader | DataView | DataObject }"
        /// <summary>
        /// retorna {Data | DataTable | DataReader | DataObject }
        /// </summary>
        /// <param name="sql">instrução sql</param>
        /// <param name="ObjectReturn">ref object</param>
        public void GetObject(String sql, ref Object ObjectReturn)
        {
            try
            {
                if (typeof(DataSet) == ObjectReturn.GetType())
                    ObjectReturn = GetDataSet(sql);
                else if (typeof(DataTable) == ObjectReturn.GetType())
                    ObjectReturn = GetDataSet(sql).Tables[0];
                else if (typeof(SqlDataReader) == ObjectReturn.GetType()
                    || typeof(FbDataReader) == ObjectReturn.GetType()
                    || typeof(MySqlDataReader) == ObjectReturn.GetType()
                    || typeof(OleDbDataReader) == ObjectReturn.GetType())
                    ObjectReturn = GetDataReader(sql);
                else if (typeof(DataView) == ObjectReturn.GetType())
                    ObjectReturn = GetDataView(GetDataSet(sql).Tables[0], "");
                else
                    throw new Exception("Objeto não conhecido...");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetDataObject -> retornar apenas um campo"
        /// <summary>
        /// retorna conteúdo de um campo do DB(object)
        /// </summary>
        /// <param name="sql">instrução sql</param>
        /// <returns>object {DataType()}</returns>
        public Object GetDataObject(String sql)
        {
            try
            {
                return GetDataObject(sql, 0);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// retorna conteúdo de um campo do DB(object)
        /// </summary>
        /// <param name="sql">instrução sql</param>
        /// <param name="index">[int/string] id/nome da coluna</param>
        /// <returns>object {DataType()}</returns>
        public Object GetDataObject(String sql, Object index)
        {
            try
            {
                IDataReader _dr = GetDataReader(sql);
                while (_dr.Read())
                {
                    if (typeof(String) == index.GetType())
                        return _dr[index.ToString()];
                    else if (typeof(Int32) == index.GetType())
                        return _dr[(Int32)index];

                    break;
                }
                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "Execute -> Executa instrução no banco"
        /// <summary>
        /// executa instrução no banco de dados
        /// </summary>
        /// <param name="cmd">instrução a executar</param>
        /// <returns>Boolean (algum registro foi afetado)</returns>
        public Boolean Execute(String cmd)
        {
            try
            {
                if (SGBD == enSGBD.Firebird)
                    return (new FbCommand(cmd, (FbConnection)cnnDB).ExecuteNonQuery() > 0);
                else if (SGBD == enSGBD.SQLServer)
                    return (new SqlCommand(cmd, (SqlConnection)cnnDB).ExecuteNonQuery() > 0);
                else if (SGBD == enSGBD.OleDb)
                    return (new OleDbCommand(cmd, (OleDbConnection)cnnDB).ExecuteNonQuery() > 0);
                else if (SGBD == enSGBD.MySql)
                    return (new MySqlCommand(cmd, (MySqlConnection)cnnDB).ExecuteNonQuery() > 0);
                else if (SGBD == enSGBD.Oracle)
                    return (new OracleCommand(cmd, (OracleConnection)cnnDB).ExecuteNonQuery() > 0);

                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "ExecuteScalar -> Executa instrução no banco"
        /// <summary>
        /// executa instrução no banco de dados
        /// </summary>
        /// <param name="cmd">instrução a executar</param>
        /// <returns>String</returns>
        public String ExecuteScalar(String cmd)
        {
            try
            {
                if (SGBD == enSGBD.Firebird)
                    return new FbCommand(cmd, (FbConnection)cnnDB).ExecuteScalar().ToString();
                else if (SGBD == enSGBD.SQLServer)
                    return new SqlCommand(cmd, (SqlConnection)cnnDB).ExecuteScalar().ToString();
                else if (SGBD == enSGBD.OleDb)
                    return new OleDbCommand(cmd, (OleDbConnection)cnnDB).ExecuteScalar().ToString();
                else if (SGBD == enSGBD.MySql)
                    return new MySqlCommand(cmd, (MySqlConnection)cnnDB).ExecuteScalar().ToString();
                else if (SGBD == enSGBD.Oracle)
                    return new OracleCommand(cmd, (OracleConnection)cnnDB).ExecuteScalar().ToString();

                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetDataSetWithParams -> retorna DataTable de stored procedure"
        /// <summary>
        /// retorna DataSet de stored procedure
        /// </summary>
        /// <param name="stored">nome do stored procedure</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSetWithParams(String stored)
        {
            try
            {
                return GetDataSetWithParams(stored, true);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// retorna DataSet de stored procedure
        /// </summary>
        /// <param name="stored">nome do stored procedure</param>
        /// <param name="clear">limpar parâmetros</param>
        /// <example>
        /// ik.Db.clDB db = new ik.Db.clDB("...")
        /// db.addParameters("@tab", "dspes");
        /// MessageBox.Show(db.GetDataSetWithParams("inftable", false).Tables[0].Rows[1][1].ToString());
        /// </example>
        /// <returns>DataSet</returns>
        public DataSet GetDataSetWithParams(String stored, Boolean clear)
        {
            try
            {
                DataSet _ds = new DataSet();
                IDataAdapter _da = null;

                if (SGBD == enSGBD.Firebird)
                {
                    _da = new FbDataAdapter(stored, (FbConnection)cnnDB);
                    ((FbDataAdapter)_da).SelectCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < _FbParms.Count; i++)
                        ((FbDataAdapter)_da).SelectCommand.Parameters.Add(_FbParms[i]);
                    ((FbDataAdapter)_da).Fill(_ds);
                    ((FbDataAdapter)_da).SelectCommand.Parameters.Clear();
                    ((FbDataAdapter)_da).Dispose();
                }
                else if (SGBD == enSGBD.SQLServer)
                {
                    _da = new SqlDataAdapter(stored, (SqlConnection)cnnDB);
                    ((SqlDataAdapter)_da).SelectCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < _SqlParms.Count; i++)
                        ((SqlDataAdapter)_da).SelectCommand.Parameters.Add(_SqlParms[i]);
                    ((SqlDataAdapter)_da).Fill(_ds);
                    ((SqlDataAdapter)_da).SelectCommand.Parameters.Clear();
                    ((SqlDataAdapter)_da).Dispose();
                }
                else if (SGBD == enSGBD.OleDb)
                {
                    _da = new OleDbDataAdapter(stored, (OleDbConnection)cnnDB);
                    ((OleDbDataAdapter)_da).SelectCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < _OleParms.Count; i++)
                        ((OleDbDataAdapter)_da).SelectCommand.Parameters.Add(_OleParms[i]);
                    ((OleDbDataAdapter)_da).Fill(_ds);
                    ((OleDbDataAdapter)_da).SelectCommand.Parameters.Clear();
                    ((OleDbDataAdapter)_da).Dispose();
                }
                else if (SGBD == enSGBD.MySql)
                {
                    _da = new MySqlDataAdapter(stored, (MySqlConnection)cnnDB);
                    ((OleDbDataAdapter)_da).SelectCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < _MySqlParms.Count; i++)
                        ((MySqlDataAdapter)_da).SelectCommand.Parameters.Add(_MySqlParms[i]);
                    ((MySqlDataAdapter)_da).Fill(_ds);
                    ((MySqlDataAdapter)_da).SelectCommand.Parameters.Clear();
                    ((MySqlDataAdapter)_da).Dispose();
                }
                else if (SGBD == enSGBD.Oracle)
                {
                    _da = new OracleDataAdapter(stored, (OracleConnection)cnnDB);
                    ((OracleDataAdapter)_da).SelectCommand.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < _OraParms.Count; i++)
                        ((OracleDataAdapter)_da).SelectCommand.Parameters.Add(_OraParms[i]);
                    ((OracleDataAdapter)_da).Fill(_ds);
                    ((OracleDataAdapter)_da).SelectCommand.Parameters.Clear();
                    ((OracleDataAdapter)_da).Dispose();
                }
                if (clear)
                    ClearParameters();

                return _ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "ExecuteProcedureWithOutput"
        /// <summary>
        /// executa procedure com output
        /// </summary>
        /// <param name="procedure">nome da procedure</param>
        /// <param name="timeoutCommand">tempo maximo de execução</param>
        /// <returns>valor output dos parametros List-Object</returns>
        public List<Object> ExecuteProcedureWithOutput(String procedure, Int32 timeoutCommand)
        {
            List<Object> _ret = new List<Object>();
            try
            {
                IDbCommand _cmd = null;

                switch (SGBD)
                {
                    case enSGBD.SQLServer:
                        _cmd = new SqlCommand(procedure, (SqlConnection)cnnDB);
                        for (int i = 0; i < _SqlParms.Count; i++)
                        {
                            _cmd.Parameters.Add(_SqlParms[i]);
                            if (_ret == null && (_SqlParms[i].Direction == ParameterDirection.InputOutput || _SqlParms[i].Direction == ParameterDirection.Output))
                                _ret.Add(_SqlParms[i]);
                        }
                        break;
                    case enSGBD.Firebird:
                        _cmd = new FbCommand(procedure, (FbConnection)cnnDB);
                        for (int i = 0; i < _FbParms.Count; i++)
                        {
                            _cmd.Parameters.Add(_FbParms[i]);
                            if (_ret == null && (_FbParms[i].Direction == ParameterDirection.InputOutput || _FbParms[i].Direction == ParameterDirection.Output))
                                _ret.Add(_FbParms[i]);
                        }
                        break;
                    case enSGBD.OleDb:
                        _cmd = new OleDbCommand(procedure, (OleDbConnection)cnnDB);
                        for (int i = 0; i < _OleParms.Count; i++)
                        {
                            _cmd.Parameters.Add(_OleParms[i]);
                            if (_ret == null && (_OleParms[i].Direction == ParameterDirection.InputOutput || _OleParms[i].Direction == ParameterDirection.Output))
                                _ret.Add(_OleParms[i]);
                        }
                        break;
                    case enSGBD.MySql:
                        _cmd = new MySqlCommand(procedure, (MySqlConnection)cnnDB);
                        for (int i = 0; i < _OleParms.Count; i++)
                        {
                            _cmd.Parameters.Add(_MySqlParms[i]);
                            if (_ret == null && (_MySqlParms[i].Direction == ParameterDirection.InputOutput || _MySqlParms[i].Direction == ParameterDirection.Output))
                                _ret.Add(_MySqlParms[i]);
                        }
                        break;
                    case enSGBD.Oracle:
                        _cmd = new OracleCommand(procedure, (OracleConnection)cnnDB);
                        for (int i = 0; i < _OraParms.Count; i++)
                        {
                            _cmd.Parameters.Add(_OraParms[i]);
                            if (_ret == null && (_OraParms[i].Direction == ParameterDirection.InputOutput || _OraParms[i].Direction == ParameterDirection.Output))
                                _ret.Add(_OraParms[i]);
                        }
                        break;
                    default:
                        break;
                }
                _cmd.CommandType = CommandType.StoredProcedure;
                _cmd.CommandTimeout = timeoutCommand;
                _cmd.ExecuteNonQuery();
                return _ret;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "AddParameters -> adiciona parâmetros"
        /// <summary>
        /// adiciona parâmetros na lista
        /// </summary>
        /// <param name="name">nome do parâmetro</param>
        /// <param name="value">valor do parâmetro</param>
        public void AddParameters(String name, Object value)
        {
            try
            {
                AddParameters(name, value, ParameterDirection.Input);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// adiciona parâmetros na lista
        /// </summary>
        /// <param name="name">nome do parâmetro</param>
        /// <param name="value">valor do parâmetro</param>
        /// <param name="direction">direção do parametro</param>
        public void AddParameters(String name, Object value, ParameterDirection direction)
        {
            try
            {
                if (SGBD == enSGBD.Firebird)
                {
                    if (!_FbParms.Exists(delegate(FbParameter p) { return p.ParameterName == name; }))
                    {
                        _FbParms.Add(new FbParameter(name, value));
                        _FbParms[_FbParms.Count - 1].Direction = direction;
                    }
                }
                else if (SGBD == enSGBD.SQLServer)
                {
                    if (!_SqlParms.Exists(delegate(SqlParameter p) { return p.ParameterName == name; }))
                    {
                        _SqlParms.Add(new SqlParameter(name, value));
                        _SqlParms[_SqlParms.Count - 1].Direction = direction;
                    }
                }
                else if (SGBD == enSGBD.OleDb)
                {
                    if (!_OleParms.Exists(delegate(OleDbParameter p) { return p.ParameterName == name; }))
                    {
                        _OleParms.Add(new OleDbParameter(name, value));
                        _OleParms[_OleParms.Count - 1].Direction = direction;
                    }
                }
                else if (SGBD == enSGBD.MySql)
                {
                    if (!_MySqlParms.Exists(delegate(MySqlParameter p) { return p.ParameterName == name; }))
                    {
                        _MySqlParms.Add(new MySqlParameter(name, value));
                        _MySqlParms[_MySqlParms.Count - 1].Direction = direction;
                    }
                }
                else if (SGBD == enSGBD.Oracle)
                {
                    if (!_OraParms.Exists(delegate(OracleParameter p) { return p.ParameterName == name; }))
                    {
                        _OraParms.Add(new OracleParameter(name, value));
                        _OraParms[_OraParms.Count - 1].Direction = direction;
                    }
                }
            }
            catch (Exception ex)
            { throw ex; }
        }
        #endregion

        #region "ClearParameters -> limpa os parâmetros"
        /// <summary>
        /// limpa os parâmetros da lista (addParameters e getDataTableWithParams)
        /// </summary>
        public void ClearParameters()
        {
            try
            {
                if (SGBD == enSGBD.Firebird)
                    _FbParms.Clear();
                else if (SGBD == enSGBD.SQLServer)
                    _SqlParms.Clear();
                else if (SGBD == enSGBD.OleDb)
                    _OleParms.Clear();
                else if (SGBD == enSGBD.MySql)
                    _MySqlParms.Clear();
                else if (SGBD == enSGBD.Oracle)
                    _OraParms.Clear();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetSchema"
        /// <summary>
        /// retorna os tiops de esquemas existentes
        /// </summary>
        /// <returns></returns>
        public DataSet GetSchema()
        {
            try
            {
                switch (SGBD)
                {
                    case enSGBD.SQLServer:
                        return this.GetTransformDataTableToDataSet(((SqlConnection)cnnDB).GetSchema());
                    case enSGBD.Firebird:
                        return this.GetTransformDataTableToDataSet(((FbConnection)cnnDB).GetSchema());
                    case enSGBD.OleDb:
                        return this.GetTransformDataTableToDataSet(((OleDbConnection)cnnDB).GetSchema());
                    case enSGBD.MySql:
                        return this.GetTransformDataTableToDataSet(((MySqlConnection)cnnDB).GetSchema());
                    case enSGBD.Oracle:
                        return this.GetTransformDataTableToDataSet(((OracleConnection)cnnDB).GetSchema());
                    default:
                        throw new Exception("SGBD não encontrado...!");
                        // agentes de cobrança;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// retorna os esquemas existentes
        /// </summary>
        /// <param name="schema">schema desejado</param>
        /// <returns></returns>
        public DataSet GetSchema(enSchema schema)
        {
            try
            {
                switch (SGBD)
                {
                    case enSGBD.SQLServer:
                        return this.GetTransformDataTableToDataSet(((SqlConnection)cnnDB).GetSchema(schema.ToString()));
                    case enSGBD.Firebird:
                        return this.GetTransformDataTableToDataSet(((FbConnection)cnnDB).GetSchema(schema.ToString()));
                    case enSGBD.OleDb:
                        return this.GetTransformDataTableToDataSet(((OleDbConnection)cnnDB).GetSchema(schema.ToString()));
                    case enSGBD.MySql:
                        return this.GetTransformDataTableToDataSet(((MySqlConnection)cnnDB).GetSchema(schema.ToString()));
                    case enSGBD.Oracle:
                        return this.GetTransformDataTableToDataSet(((OracleConnection)cnnDB).GetSchema(schema.ToString()));
                    default:
                        throw new Exception("SGBD não encontrado...!");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetPrimaryKey -> String/Object[]"
        public String GetPrimaryKey(String tab)
        {
            DataTable _dt = null;
            Object[] _pk;
            String _ret = "";

            try
            {
                if (SGBD == enSGBD.Firebird)
                    _dt = GetDataSet("SELECT FIRST 0 * FROM " + tab, 0).Tables[0];
                else if (SGBD == enSGBD.SQLServer)
                    _dt = GetDataSet("SELECT TOP 1 * FROM " + tab, 0).Tables[0];
                else if (SGBD == enSGBD.Oracle)
                    _dt = GetDataSet("SELECT * FROM " + tab + " where rownum = 1", 0).Tables[0];
                else if (SGBD == enSGBD.MySql)
                    throw new Exception("Verificar implementação");
                else if (SGBD == enSGBD.OleDb)
                {
                    _dt = ((OleDbConnection)cnnDB).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new Object[] { null, null, tab });
                    // following is a lengthy form of the number '3'
                    Int32 columnOrdinalForName = _dt.Columns["COLUMN_NAME"].Ordinal;
                    foreach (DataRow r in _dt.Rows)
                    {
                        if (_ret != "") _ret += ", ";
                        _ret += r.ItemArray[columnOrdinalForName].ToString();
                    }
                    return _ret;
                }

                if (_dt.PrimaryKey.Length == 0)
                {
                    if (SGBD == enSGBD.SQLServer)
                    {
                        DataSet _ds = this.GetTransformDataTableToDataSet(_dt);
                        DataColumn[] _dcol = this.GetPrimaryKeys_InformationSchema(_ds, tab);
                        _dt.PrimaryKey = _dcol;
                    }
                    else
                        throw new Exception("Não consegui localizar a chave primária...");
                }
                _pk = new Object[_dt.PrimaryKey.Length];

                foreach (DataColumn pk in _dt.PrimaryKey)
                {
                    if (_ret != "") _ret += ", ";
                    _ret += pk.ColumnName;
                }
                return _ret;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Object[] GetPrimaryKeyObj(String tab)
        {
            DataTable _dt;
            Object[] _pk;
            Int32 _chaves;
            try
            {
                _dt = GetDataSet("SELECT FIRST 0 * FROM " + tab, 0).Tables[0];
                _pk = new Object[_dt.PrimaryKey.Length];

                _chaves = 0;
                foreach (DataColumn pk in _dt.PrimaryKey)
                {
                    _pk[_chaves] = pk.ColumnName;
                    _chaves++;
                }
                return _pk;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// retorna uma coleção de DataColumn com as colunas que compoe a chave primária
        /// </summary>
        /// <param name="ds">dataset</param>
        /// <param name="tableName">nome da tabela</param>
        /// <returns>DataColumn[]</returns>
        public DataColumn[] GetPrimaryKeys_InformationSchema(DataSet ds, String tableName)
        {
            DataColumn[] _pks = null;
            try
            {
                if (ds.Tables[0].PrimaryKey.Length == 0)
                {
                    if (SGBD == enSGBD.SQLServer)
                    {
                        Int32 i = 0;
                        DataTable _dt = new DataTable();

                        String _sql = "SELECT 	c.COLUMN_NAME " +
                                      "FROM     INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk " +
                                      "JOIN     INFORMATION_SCHEMA.KEY_COLUMN_USAGE c " +
                                      "ON       c.TABLE_NAME      = pk.TABLE_NAME " +
                                      "AND	    c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME " +
                                      "WHERE    CONSTRAINT_TYPE   = 'PRIMARY KEY' " +
                                      "AND      pk.TABLE_NAME     = '" + tableName + "'";

                        SqlDataAdapter _da = new SqlDataAdapter(_sql, (SqlConnection)cnnDB);
                        _da.Fill(_dt);
                        _pks = new DataColumn[_dt.Rows.Count];
                        _da.Dispose();

                        foreach (DataRow row in _dt.Rows)
                        {
                            foreach (DataColumn colSrc in ds.Tables[0].Columns)
                            {
                                if (row["COLUMN_NAME"].ToString() == colSrc.ColumnName)
                                {
                                    _pks[i++] = colSrc;
                                    break;
                                }
                            }
                        }
                    }
                    else
                        throw new Exception("SGBD não suportado para esta função...");
                }
                else
                {
                    _pks = ds.Tables[0].PrimaryKey;
                }

                return _pks;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region "GetPkSearch"
        /// <summary>
        /// retorna a chave PK com os campos para fazer pesquisas
        /// </summary>
        /// <param name="pk">pks... ("codigo, id, ...")</param>
        /// <param name="dr">linha de dados</param>
        /// <returns></returns>
        public String GetPkSearch(String pk, DataRow dr)
        {
            try
            {
                String[] _pks = pk.Split(',');
                String _ret = "";
                foreach (String s in _pks)
                {
                    _ret += _ret == "" ? "" : " AND ";
                    _ret += s.Trim() + " = " + this.GetFormat(dr[s.Trim()]);
                }
                return _ret.Replace("?", "'");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GeraPLSQL -> Constroe a instrução t-sql"
        /// <summary>
        /// Retorna a string para realizar o INSERT/UPDATE
        /// </summary>
        /// <param name="drSource">dados de origem</param>
        /// <param name="drSodrDesturce">dados de destino</param>
        /// <param name="newRecord">Novo registro (sim/não)</param>
        /// <param name="tab">Tabela para atualizar</param>
        /// <param name="chv">Chave do update</param>
        /// <returns>Retorna string t-sql</returns>
        public String GeraTransactSQL(IDataReader drSource, IDataReader drDest, Boolean newRecord, String tab, String chv)
        {
            String _sql = "";
            String _sql1 = "";
            try
            {
                for (int i = 0; i < drSource.FieldCount; i++)
                {
                    if (newRecord) //insert
                    {
                        if (_sql != "") _sql += ", ";
                        if (_sql1 != "") _sql1 += ", ";
                        _sql += drSource.GetName(i);
                        _sql1 += this.GetFormat(drSource[i]).Replace("'", "`");
                    }
                    else //update
                    {
                        if (drSource[i].ToString() != drDest[i].ToString())
                        {
                            _sql += _sql == "" ? "" : ", ";
                            _sql += drSource.GetName(i) + " = " + this.GetFormat(drSource[i]).Replace("'", "`");
                        }
                    }
                }

                if (_sql != "")
                {
                    if (_sql.Substring(_sql.Length - 2, 1) == ",")
                        _sql = _sql.Substring(0, _sql.Length - 2);

                    if (newRecord)
                        _sql = "INSERT INTO " + tab + " (" + _sql + ") VALUES (" + _sql1 + ")";
                    else
                        _sql = "UPDATE " + tab + " SET " + _sql + " WHERE " + chv;
                }
                return _sql.Replace("?", "'");

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "CreateDataTable -> DataTable"
        /// <summary>
        /// cria um DataTable
        /// </summary>
        /// <param name="colName">params: coluna, type</param>
        /// <example>DataTable _dt = cd.CreateDataTable("id", typeof(Int32), "Nome", typeof(String));</example>
        /// <returns>DataTable</returns>
        public DataTable CreateDataTable(params Object[] colName)
        {
            try
            {
                DataTable _dt = new DataTable();
                for (int i = 0; i < colName.Length; i = i + 2)
                    _dt.Columns.Add(new DataColumn(colName[i].ToString(), (Type)colName[i + 1]));
                return _dt;
            }
            catch (Exception ex)
            { throw ex; }
        }
        #endregion

        #region "AddColumnDataTable -> DataTable"
        /// <summary>
        /// cria uma nova coluna em um DataTable
        /// </summary>
        /// <param name="dt">DataTable para adicionar a coluna</param>
        /// <param name="col">nome da coluna</param>
        /// <param name="maxLength">tamanho máximo</param>
        /// <param name="isNull">aceita valor nulo</param>
        /// <param name="isUnique">chave única</param>
        /// <param name="isReadOnly">campo é somente leitura</param>
        /// <returns>DataTable</returns>
        public DataTable AddColumnDataTable(DataTable dt, String col, Int32 maxLength, Boolean isNull, Boolean isUnique, Boolean isReadOnly)
        {
            try
            {
                DataColumn _dc = new DataColumn();

                _dc.ColumnName = col;
                if (maxLength != 0)
                    _dc.MaxLength = maxLength;
                _dc.AllowDBNull = isNull;
                _dc.Unique = isUnique;
                _dc.ReadOnly = isReadOnly;
                dt.Columns.Add(_dc);

                return dt;
            }
            catch (Exception ex)
            { throw ex; }
        }
        #endregion

        #region "AddRowDataTable -> void"
        /// <summary>
        /// adiciona nova linha em um DataTable
        /// </summary>
        /// <param name="dt">nome do DataTable</param>
        /// <param name="col_val">Coluna, valor</param>
        public void AddRowDataTable(DataTable dt, params Object[] col_val)
        {
            try
            {
                DataRow r = dt.NewRow();
                for (int i = 0; i < col_val.Length; i = i + 2)
                {
                    try
                    {
                        r[col_val[i].ToString()] = col_val[i + 1];
                    }
                    catch (Exception)
                    {
                        r[col_val[i].ToString()] = this.GetFormat(col_val[i + 1]);
                    }
                }
                dt.Rows.Add(r);
            }
            catch (Exception ex)
            { throw ex; }
        }
        #endregion

        #region "GetTransformDataReaderToDataSet - getTransformDataReaderToDataTable"
        /// <summary>
        /// transforma um SqlDataReader em um DataTable
        /// </summary>
        /// <param name="dr">nome do SqlDataReader</param>
        /// <returns>DataTable</returns>
        public DataTable GetTransformDataReaderToDataTable(IDataReader dr)
        {
            try
            {
                DataSet _ds = new DataSet();
                GetTransformDataReaderToDataSet(_ds, "noTableName", dr);
                return _ds.Tables["noTableName"];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// transforma um SqlDataReader em um DataSet
        /// </summary>
        /// <param name="ds">DataSet de destino</param>
        /// <param name="table">nome da tabela</param>
        /// <param name="dr">nome do SqlDataReader</param>
        public void GetTransformDataReaderToDataSet(DataSet ds, String table, IDataReader dr)
        {
            try
            {
                Type tipoDataReader = dr.GetType();
                String nomeTipo = tipoDataReader.FullName.Replace("DataReader", "DataAdapter");
                Type tipoDataAdapter = tipoDataReader.Assembly.GetType(nomeTipo);
                Object da = Activator.CreateInstance(tipoDataAdapter);

                Object[] args = { ds, table, dr, 0, 999999 };
                tipoDataAdapter.InvokeMember("Fill", System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, da, args);
                dr.Close();
            }
            catch (Exception ex)
            { throw ex; }
        }
        #endregion

        #region "GetTransformDataTableToDataSet"
        /// <summary>
        /// transforma um DataTable num DataSet
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <returns>DataSet</returns>
        public DataSet GetTransformDataTableToDataSet(DataTable dt)
        {
            try
            {
                DataSet _ds = new DataSet();
                _ds.Tables.Add(dt);
                return _ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// transforma um DataTable num DataSet
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="dsDes">DataSet destino</param>
        /// <returns>DataSet</returns>
        public DataSet GetTransformDataTableToDataSet(DataTable dt, DataSet dsDes)
        {
            try
            {
                dsDes.Tables.Add(dt);
                return dsDes;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "SortDataTable -> DataTable"
        /// <summary>
        /// retorna DataTable ordenada pela coluna [col]
        /// </summary>
        /// <param name="dt">nome do DataTable</param>
        /// <param name="col">index das colunas para ordenar</param>
        /// <returns>DataTable</returns>
        public DataTable SortDataTable(DataTable dt, params int[] col)
        {
            try
            {
                DataTable _dt = null;
                String _str = "";
                _dt = dt.Copy();
                for (int i = 0; i < col.Length; i++)
                {
                    if (i > 0) _str += ", ";
                    _str += _dt.Columns[col[i]].ColumnName + " asc";
                }
                _dt.DefaultView.Sort = _str;
                return _dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// retorna DataTable ordenada pela coluna [col]
        /// </summary>
        /// <param name="dt">nome do DataTable</param>
        /// <param name="col">string das colunas para ordenar</param>
        /// <returns>DataTable</returns>
        public DataTable sortDataTable(DataTable dt, params String[] col)
        {
            try
            {
                DataTable _dt = null;
                String _str = "";
                _dt = dt.Copy();
                for (int i = 0; i < col.Length; i++)
                {
                    if (i > 0) _str += ", ";
                    _str += _dt.Columns[col[i]] + " asc";
                }
                _dt.DefaultView.Sort = _str;
                return _dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion


        #region "GetDataSetDifferences"
        /// <summary>
        /// retorna as diferenças entre DataSet´s (by Ivan Kruger)
        /// </summary>
        /// <param name="ds1">DataSet 1</param>
        /// <param name="ds2">DataSet 2</param>
        /// <param name="pkOnly">pesquisa somente pela chave primaria ou todos os campos</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSetDifferences(DataSet ds1, DataSet ds2, Boolean pkOnly)
        {
            try
            {
                Boolean dif = false;
                Object[] pk = new Object[ds2.Tables[0].PrimaryKey.Length];
                DataRow dr = null;
                DataSet ds3 = ds1.Clone();
                ds3.Clear();
                foreach (DataRow r in ds1.Tables[0].Rows)
                {
                    dif = false;

                    for (int i = 0; i < pk.Length; i++)
                        pk[i] = r[i];
                        //pk[i] = r[ds2.Tables[0].PrimaryKey[i].Caption];

                    dr = ds2.Tables[0].Rows.Find(pk);
                    if (dr == null)
                        dif = true;
                    else if (!pkOnly)
                    {
                        foreach (DataColumn c in ds1.Tables[0].Columns)
                        {
                            if (r[c.ColumnName].ToString() != dr[c.ColumnName].ToString())
                            {
                                dif = true;
                                break;
                            }
                        }
                    }
                    if (dif)
                        ds3.Tables[0].Rows.Add(r.ItemArray);
                }
                return ds3;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "SetDataSetPrimaryKey"
        /// <summary>
        /// atribui uma chave primaria a um DataSet 
        /// [ SetPrimaryKey(ref ds1, new String[] { "cd_pes", "name" }); ]
        /// </summary>
        /// <param name="ds">ref DataSet</param>
        /// <param name="pks">Coleção de Strings</param>
        public void SetDataSetPrimaryKey(ref DataSet ds, String[] pks)
        {
            try
            {
                int i = 0;
                DataColumn[] _dc = new DataColumn[pks.Length];
                foreach (String s in pks)
                {
                    _dc[i] = ds.Tables[0].Columns[s];
                    i++;
                }
                ds.Tables[0].PrimaryKey = _dc;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "DumpGenerate"
        /// <summary>
        /// retorna o dump de uma instrução
        /// </summary>
        /// <param name="dr">IDataReader - dados de origem</param>
        /// <param name="tab">Tabela origem</param>
        /// <param name="keepIdentity">mande campos ideitity</param>
        /// <returns>Retorna string t-sql</returns>
        public String DumpGenerate(IDataReader dr, String tab, Boolean keepIdentity)
        {
            String _field = "";
            String _column = "";
            
            try
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    //verifica se o campo é IDENTITY
                    if ((Boolean)dr.GetSchemaTable().Rows[i]["IsIdentity"] && !keepIdentity)
                        continue;

                    if (_field != "") _field += ", ";
                    if (_column != "") _column += ", ";
                    _field += dr.GetName(i);
                    _column += GetFormat(dr[i]).Replace("'", "`");
                }
                if (_field.Substring(_field.Length - 2, 1) == ",")
                    _field = _field.Substring(0, _field.Length - 2);

                return ("INSERT INTO " + tab + " (" + _field + ") VALUES (" + _column + ")").Replace("?", "'");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "Transform_IDataReader_DataTable"
        /// <summary>
        /// transforma um IDataReader em um DataTable
        /// </summary>
        /// <param name="dr">IDataReader origem</param>
        /// <param name="options">LoadOption opções</param>
        /// <returns>DataTable</returns>
        public DataTable Transform_IDataReader_DataTable(IDataReader dr, LoadOption options)
        {
            try
            {
                DataTable _dt = new DataTable();
                _dt.Load(dr, options);
                return _dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}




namespace ik.FunctionsDB
{
    #region enum
    public enum enRegister
    {
        Register = 0,
        Unregister = 1
    }
    public enum enEncryption
    {
        Encriptar = 0,
        Descriptar = 1
    }
    #endregion

    /// <summary>
    /// funções gerais para ser utilizadas com a classe de banco de dados.
    /// </summary>
    public class clFunctionsDB
    {
        #region "ArrayChange"
        /// <summary>
        /// redimensiona um arrray
        /// </summary>
        /// <param name="arr">array de strings</param>
        /// <returns>String[]</returns>
        public String[] ArrayChange(String[] arr)
        {
            try
            {
                Array.Resize<String>(ref arr, arr.Length + 1);
                return arr;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GeraTransactSQL_ds -> Constroe a instrução t-sql"
        /// <summary>
        /// Retorna a string para realizar o INSERT/UPDATE
        /// </summary>
        /// <param name="drSource">dados de origem</param>
        /// <param name="drSodrDesturce">dados de destino</param>
        /// <param name="newRecord">Novo registro (sim/não)</param>
        /// <param name="tab">Tabela para atualizar</param>
        /// <param name="chv">Chave do update</param>
        /// <returns>Retorna string t-sql</returns>
        public String GeraTransactSQL_ds(DataRow drSource, DataRow drDest, String tab, String chv, Boolean insertIndentity)
        {
            String _sql = "";
            String _sql1 = "";
            try
            {
                foreach (DataColumn c in drSource.Table.Columns)
                {
                    //verifica se o campo é IDENTITY
                    if (c.AutoIncrement && !insertIndentity)
                        continue;

                    if (drDest == null) //insert
                    {
                        _sql += _sql == "" ? "" : ", ";
                        _sql1 += _sql1 == "" ? "" : ", ";
                        _sql += c.ToString();
                        _sql1 += this.GetFormat(drSource[c.ToString()]).Replace("'", "`");
                    }
                    else //update
                    {
                        //replace special for Firebird
                        if (drSource[c].ToString().Replace(" 00:00:00", "").Replace("\r", "").Replace("\n", "") != drDest[c.ToString()].ToString().Replace(" 00:00:00", "").Replace("\r", "").Replace("\n", ""))
                        {
                            _sql += _sql == "" ? "" : ", ";
                            _sql += c.ToString() + " = " + this.GetFormat(drSource[c.ToString()]).Replace("'", "`");
                        }
                    }
                }

                if (_sql != "")
                {
                    if (_sql.Substring(_sql.Length - 2, 1) == "," && !_sql.EndsWith("?"))
                        _sql = _sql.Substring(0, _sql.Length - 2);

                    if (drDest == null) //insert
                        _sql = "INSERT INTO " + tab + " (" + _sql + ") VALUES (" + _sql1 + ")";
                    else
                        _sql = "UPDATE " + tab + " SET " + _sql + " WHERE " + chv;
                }
                return _sql.Replace("?", "'");

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetFormat -> string"
        /// <summary>
        /// formata os campos de acordo com o TYPE
        /// </summary>
        /// <param name="obj">objeto com o valor para formatar</param>
        /// <returns>item formatado conforme GetType()</returns>
        public String GetFormat(object obj)
        {
            if (obj == null) return "null";

            String _return = ((object)obj).ToString();

            if (obj.GetType() == typeof(System.String))
                return "?" + _return.Replace("?", "_") + "?";

            else if (obj.GetType() == typeof(System.Decimal) || obj.GetType() == typeof(System.Single) || obj.GetType() == typeof(System.Double))
                return Decimal.Parse(_return).ToString().Replace(",", ".");

            else if (obj.GetType() == typeof(System.Int16))
                return Int16.Parse(_return).ToString();

            else if (obj.GetType() == typeof(System.Int32))
                return Int32.Parse(_return).ToString();

            else if (obj.GetType() == typeof(System.Int64))
                return Int64.Parse(_return).ToString();

            else if (obj.GetType() == typeof(System.Boolean))
                if ((Boolean)obj) return "1";
                else return "0";

            else if (obj.GetType() == typeof(System.DateTime))
                return "?" + ((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss") + "?";

            else if (obj.GetType() == typeof(System.DBNull))
                return "NULL";

            else
                return _return;
        }
        #endregion

        #region "CrypTo -> Criptografia de string"
        /// <summary>
        /// Criptografia e descriptografia de string
        /// </summary>
        /// <param name="text">Texto a ser encriptado</param>
        /// <param name="crypt">True-Encrypt; False-Decrypt</param>
        /// <returns>Retorna string encriptada/decriptada</returns>
        /// <example>Crypto("Texto", True)</example>
        public String CrypTo(String text, enEncryption crypt)
        {
            try
            {
                if (crypt == enEncryption.Encriptar)
                    return Convert.ToBase64String(Encoding.ASCII.GetBytes(text));
                else
                    return Encoding.ASCII.GetString(Convert.FromBase64String(text));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static String CrypTo2(String text, enEncryption crypt)
        {
            try
            {
                if (crypt == enEncryption.Encriptar)
                    return Convert.ToBase64String(Encoding.ASCII.GetBytes(text));
                else
                    return Encoding.ASCII.GetString(Convert.FromBase64String(text));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}